﻿using UnityEngine;
using NPOI.SS.UserModel;
using System;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using LitJson;

public static class ExcelHelper
{
    /// <summary>
    ///  读取文件得到一个工作簿对象 (兼容xls和xlsx)
    /// </summary>
    /// <param name="path"></param>
    /// <returns></returns>
    public static IWorkbook ReadWorkBook(string path)
    {
        IWorkbook workbook = null;
        using (var fs = File.OpenRead(path))
        {
            string pathStr = path.ToLower();
            if (pathStr.EndsWith(".xls"))
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (pathStr.EndsWith(".xlsx"))
            {
                workbook = new XSSFWorkbook(fs);
            }
        }
        return workbook;
    }

    /// <summary>
    ///  遍历一张表中的每一行,注意行为null ,空行需要在委托中判断,func返回值如果为false则停止遍历
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="func"></param>
    public static void ForEveryRow(ISheet sheet, Func<IRow, bool> func)
    {
        int lastRowNum = sheet.LastRowNum;//最大行号,因为从0开始,所以比我们再软件中看到的小1
        for (int r = 0; r <= lastRowNum; r++)//行必须能取到lastRowNum
        {
            IRow row = sheet.GetRow(r);
            if (!func(row))
            {
                return;
            }
        }
    }

    /// <summary>
    ///  遍历每一个行中的格子,注意在委托中判断格子为null的情况,func返回值如果为false则停止遍历
    /// </summary>
    /// <param name="row"></param>
    /// <param name="func"></param>
    public static void ForEveryCell(IRow row, Func<ICell, bool> func)
    {
        int lastCellNum = row.LastCellNum;
        for (int c = 0; c < lastCellNum; c++)
        {
            ICell cell = row.GetCell(c);
            if (!func(cell))
            {
                return;
            }
        }
    }
    //直接取指定格子中的字符串
    public static void ReadCellOnce(ISheet sheet, int r, int c)
    {
        IRow row = sheet.GetRow(r);//得到一行
        ICell cell = row.GetCell(c);//得到一个格子   注意,如果一行中没有一个格子有值,这一行就是null
        Debug.Log(cell.ToString());   //注意如果格子是空的,cell可能为Null
                                      //Debug.Log(cell.StringCellValue);//只能是单元格格式 本身就是字符串才能这么获取字符串,否则只能用上面的方法获取字符串
    }

    public static string ExcelToJson(ISheet sheet)
    {
        IRow row0 = sheet.GetRow(0);
        JsonWriter writer = new JsonWriter();
        writer.WriteArrayStart();
        ForEveryRow(sheet, row =>
        {
            if (row == null || row.RowNum == 0) return true;//跳过空行和表头行
            writer.WriteObjectStart();
            ForEveryCell(row, cell =>
            {
                if (cell == null) return true;
                string cellString = cell.ToString();
                writer.WritePropertyName(row0.GetCell(cell.ColumnIndex).ToString());//属性命名

                int _Int;
                float _Float;
                if (int.TryParse(cellString, out _Int))
                {
                    writer.Write(_Int);
                }
                else if (float.TryParse(cellString, out _Float))
                {
                    writer.Write(_Float);
                }
                else
                {
                    writer.Write(cellString);
                }
                return true;
            });
            writer.WriteObjectEnd();
            return true;
        });
        writer.WriteArrayEnd();
        string jsonStr = writer.ToString();
        return jsonStr;
    }
}